package reportcs

import (
	"bufio"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"io"
	"os"
	"path/filepath"
	"sort"
	"strings"

	"gitee.com/haodreams/golib/estring"
	"gitee.com/haodreams/libs/sliceutil"
	"gitee.com/haodreams/report"
	"golang.org/x/text/encoding/simplifiedchinese"
	"gorm.io/gorm"
)

// Index .
func (m *TableController) Index() {
	tab := m.GetValidTable()
	if tab == nil {
		m.Error("没有这个表")
		return
	}
	tab.Fix()
	if m.IsPost() {
		m.Dataset(tab)
		return
	}
	m.Map["TableName"] = tab.Name
	if tab.Template != "" {
		m.Display(m.ControllerName + "/" + tab.Template)
		return
	}

	m.Display()
}

// GetData 请求表数据
func (m *TableController) GetData() {
	if m.IsPost() {
		tab := m.GetValidTable()
		if tab == nil {
			m.Error("没有这个表")
			return
		}
		tab.Fix()
		m.Dataset(tab)
		return
	}
	m.GetTableJSON()
}

func (m *TableController) GetTableJSON() {
	// tab := m.getValidTable()
	// if tab == nil {
	// 	m.Error("表不存在")
	// 	return
	// }
	// tab.Fix()
	// m.JSON(200, tab)

	path := m.Param("path")
	ss := strings.Split(path, "/")
	if len(ss) < 3 {
		m.Error("path error")
		return
	}
	path = pathJsJSON + ss[len(ss)-1]
	isJs := strings.HasSuffix(path, ".js")
	if !isJs {
		uri := m.Request.URL.RequestURI()
		if strings.HasSuffix(uri, ".js") {
			pos := strings.LastIndex(uri, "/")
			name := uri[pos+1:]
			path = pathJsJSON + name
			isJs = true
		} else {
			path += ".json"
		}
	}

	jsonData, err := os.ReadFile(path)
	if err != nil {
		m.Error(err.Error())
		return
	}

	if isJs {
		m.Writer.Header().Set("Content-Type", "application/javascript")
	} else {
		m.Header("Content-Type", "application/json; charset=utf-8")
	}
	m.Writer.Write(jsonData)
}

// MakeTableJSON 生成table的json文件到table/tmp 目录下
// TODO 生成table的json文件到table/tmp 目录下
func (m *TableController) MakeTableJSON() {

}

// GetJSON 请求json字符串
func (m *TableController) GetJSON() {
	m.GetTableJSON()
}

func (m *TableController) GetValidTable() (tab *report.Table) {
	path := m.Param("path")
	ss := strings.SplitN(path, "/", 3)
	if len(ss) < 3 {
		return nil
	}
	path = pathJsJSON + ss[2]
	tab = m.GetTable(path)
	return tab
}

// Insert 只是插入入数据不做数据更新
func (m *TableController) Insert() {
	m.InsertDB(defaultDB)
}

// InsertDB 插入数据到指定的DB
func (m *TableController) InsertDB(db *gorm.DB) {
	tab := m.GetValidTable()
	if tab == nil {
		m.Error("表不存在")
		return
	}
	tab.Fix()
	header, err := m.FormFile("file")
	if err != nil {
		m.Error(err.Error())
		return
	}
	f, err := header.Open()
	if err != nil {
		m.Error(err.Error())
		return
	}
	defer f.Close()

	code := estring.GetFileCode(f)

	insert := report.NewInsert(tab)

	var buf io.Reader
	buf = f
	//加入编码转换
	if code == "GBK" {
		decoder := simplifiedchinese.GBK.NewDecoder()
		buf = decoder.Reader(f)
	}

	err = insert.Insert(db, buf)
	if err != nil {
		m.Error(err.Error())
		return
	}
	m.Msg("OK")
}

// TableExportDB 导出指定表的数据
// 可以指定主键的
func (m *TableController) TableExportDB(db *gorm.DB) {
	tab := m.GetValidTable()
	if tab == nil {
		m.Error("表不存在")
		return
	}
	tab.Fix()

	path := m.Param("path")
	tableName := filepath.Base(path)
	sql := "SELECT * FROM " + tableName
	primary := m.GetParam().GetTrimString("keys")
	if primary != "" && tab.Primary != "" {
		ss := strings.Split(primary, ",")
		switch len(ss) {
		case 0:
		case 1:
			col := tab.GetColumn(tab.Primary)
			if col != nil {
				sql += " WHERE " + tab.Primary + " = " + col.GetValue(ss[0])
			}
		default:
			col := tab.GetColumn(tab.Primary)
			if col != nil {
				vals := make([]string, 0, len(ss))
				for i := range ss {
					vals = append(vals, col.GetValue(ss[i]))
				}
				sql += " WHERE " + tab.Primary + " IN (" + strings.Join(vals, ",") + ")"
			}
		}
	}

	rows := []map[string]interface{}{}
	//db.Raw(sql).ScanRows()
	err := db.Raw(sql).Find(&rows).Error
	if err != nil {
		m.Error(err.Error())
		return
	}
	//如果没有数据下载列头（模板）
	// if len(rows) == 0 {
	// 	m.Error("NO data")
	// 	return
	// }
	m.Writer.Header().Add("Content-Disposition", "attachment; filename="+tableName+".csv") //fmt.Sprintf("attachment; filename=%s", filename)对下载的文件重命名
	m.Writer.Header().Add("Content-Type", "application/octet-stream")
	//m.Writer.Write(buf.Bytes())

	cols := make([]string, len(tab.Columns))
	i := 0
	for _, col := range tab.Columns {
		if col.Name != "" {
			cols[i] = col.Name
			i++
		}
	}
	cols = cols[:i]

	encode := simplifiedchinese.GBK.NewEncoder()

	buf := bufio.NewWriter(encode.Writer(m.Writer))

	csvbuf := csv.NewWriter(buf)

	csvbuf.Write(cols)
	vals := make([]string, len(cols))

	for _, row := range rows {
		for i := 0; i < len(cols); i++ {
			v := row[cols[i]]
			s := fmt.Sprint(v)
			if v == nil {
				s = ""
			}
			vals[i] = s
		}
		csvbuf.Write(vals)
	}
	csvbuf.Flush()
	buf.Flush()
}

// TableExport .
func (m *TableController) TableExport() {
	m.TableExportDB(defaultDB)
}

// FixWhere 修复where条件
func (m *TableController) FixWhere() {
	params := m.GetParam().GetMap()
	for key, value := range params {
		if strings.HasPrefix(key, "search_") {
			value = strings.TrimSpace(value)
			if value != "" {
				key = strings.TrimPrefix(key, "search_")
				if key == "" {
					continue
				}
				//key = strings.ReplaceAll(key, "Id", "ID")
				params["search_"+key] = value
			}
		} else if strings.HasPrefix(key, "where_") {
			value = strings.TrimSpace(value)
			if value != "" {
				key = strings.TrimPrefix(key, "where_")
				if key == "" {
					continue
				}
				//key = strings.ReplaceAll(key, "Id", "ID")
				params["where_"+key] = value
			}
		}
	}
}

// TableDeleteDB 删除指定表的数据
// 可以指定主键
func (m *TableController) TableDeleteDB(db *gorm.DB) {
	tab := m.GetValidTable()
	if tab == nil {
		m.Error("表不存在")
		return
	}
	tab.Fix()

	path := m.Param("path")
	tableName := filepath.Base(path)
	sql := "DELETE FROM " + tableName
	primary := m.GetParam().GetTrimString("keys")
	if primary != "" && tab.Primary != "" {
		ss := strings.Split(primary, ",")
		switch len(ss) {
		case 0:
		case 1:
			col := tab.GetColumn(tab.Primary)
			if col != nil {
				sql += " WHERE " + tab.Primary + " = " + col.GetValue(ss[0])
			}
		default:
			col := tab.GetColumn(tab.Primary)
			if col != nil {
				vals := make([]string, 0, len(ss))
				for i := range ss {
					vals = append(vals, col.GetValue(ss[i]))
				}
				sql += " WHERE " + tab.Primary + " IN (" + strings.Join(vals, ",") + ")"
			}
		}
	}
	db = db.Exec(sql)
	err := db.Error
	if err != nil {
		m.Error(err.Error())
		return
	}
	m.Msg(fmt.Sprintf("影响行数:%d", db.RowsAffected))
}

// TableDelete .
func (m *TableController) TableDelete() {
	m.TableDeleteDB(defaultDB)
}

// TableInsertOrUpdateDB 插入表数据，有则更新，无则插入
func (m *TableController) TableInsertOrUpdateDB(db *gorm.DB) {
	tab := m.GetValidTable()
	if tab == nil {
		m.Error("表不存在")
		return
	}
	tab.Fix()

	if tab.Name == "" {
		path := m.Param("path")
		tab.Name = filepath.Base(path)
	}

	header, err := m.FormFile("file")
	if err != nil {
		m.Error(err.Error())
		return
	}
	f, err := header.Open()
	if err != nil {
		m.Error(err.Error())
		return
	}
	defer f.Close()

	code := estring.GetFileCode(f)

	insert := report.NewInsert(tab)

	var buf io.Reader
	buf = f
	//加入编码转换
	if code == "GBK" {
		decoder := simplifiedchinese.GBK.NewDecoder()
		buf = decoder.Reader(f)
	}

	err = insert.InsertOrUpdate(db, tab.Primary, buf)
	if err != nil {
		m.Error(err.Error())
		return
	}
	m.Msg("OK")
}

// TableInsertOrUpdate .
func (m *TableController) TableInsertOrUpdate() {
	m.TableInsertOrUpdateDB(defaultDB)
}

// ExportDB 导出任意表的数据
func (m *TableController) ExportDB(db *gorm.DB) {
	path := m.Param("path")
	tableName := filepath.Base(path)
	rows := []map[string]interface{}{}
	sql := "SELECT * FROM " + tableName
	err := db.Raw(sql).Find(&rows).Error
	if err != nil {
		m.Error(err.Error())
		return
	}
	if len(rows) == 0 {
		m.Error("NO data")
		return
	}
	m.Writer.Header().Add("Content-Disposition", "attachment; filename="+tableName+".csv") //fmt.Sprintf("attachment; filename=%s", filename)对下载的文件重命名
	m.Writer.Header().Add("Content-Type", "application/octet-stream")
	//m.Writer.Write(buf.Bytes())
	cols := make([]string, len(rows[0]))
	i := 0
	for col := range rows[0] {
		cols[i] = col
		i++
	}
	sort.Strings(cols)

	encode := simplifiedchinese.GBK.NewEncoder()

	buf := bufio.NewWriter(encode.Writer(m.Writer))

	csvbuf := csv.NewWriter(buf)

	csvbuf.Write(cols)
	vals := make([]string, len(cols))

	for _, row := range rows {
		for i := 0; i < len(cols); i++ {
			v := row[cols[i]]
			s := fmt.Sprint(v)
			if v == nil {
				s = ""
			}
			vals[i] = s
		}
		csvbuf.Write(vals)
	}
	csvbuf.Flush()
	buf.Flush()
}

// Export .
func (m *TableController) Export() {
	m.ExportDB(defaultDB)
}

// ClearDB 清空表数据
func (m *TableController) ClearDB(db *gorm.DB) {
	path := m.Param("path")
	tableName := filepath.Base(path)
	sql := "DELETE FROM " + tableName
	db = db.Exec(sql)
	err := db.Error
	if err != nil {
		m.Error(err.Error())
		return
	}
	//num, err := rv.RowsAffected()
	m.Msg(fmt.Sprintf("影响行数:%d", db.RowsAffected))
}

// Clear .
func (m *TableController) Clear() {
	m.ClearDB(defaultDB)
}

// ToWhereSortPage 转换为WhereSort条件的页面
func (m *TableController) ToWhereSortPage(items interface{}, callback func(interface{}) interface{}, isReg ...bool) {
	//先过滤 后排序
	params := m.GetParam().GetMap()

	newItems := sliceutil.WhereMap(params, items, isReg...)

	sliceutil.OrderBy(newItems, params["sortKey"], params["sortOrder"])

	m.Page(newItems, callback)
}

// GetTable 获取表的模板数据
func (m *TableController) GetTable(path string) (tab *report.Table) {
	//log.Println(path)
	jsonData, err := os.ReadFile(path + ".json")
	if err != nil {
		m.Error(err.Error())
		return
	}
	tab = new(report.Table)
	err = json.Unmarshal(jsonData, &tab)
	if err != nil {
		m.Error(err.Error())
		return nil
	}
	return
}

// DatasetDB 数据集
func (m *TableController) DatasetDB(db *gorm.DB, tab *report.Table) {
	total := int64(0)
	var vals []interface{}
	where := ""

	cond := m.GetParam().GetTrimString("where")
	if cond != "" {
		where += cond + " "
	}
	param := m.GetParam()
	mp := param.GetMap()
	for key, value := range mp {
		if strings.HasPrefix(key, "search_") {
			value = strings.TrimSpace(value)
			if value != "" {
				key = strings.TrimPrefix(key, "search_")
				oper, val := tab.GetOper(key, value)
				where += fmt.Sprintf(" AND %s %s ?", report.GetDialect()+key+report.GetDialect(), oper)
				vals = append(vals, val)
			} else if strings.HasPrefix(key, "where_") {
				value = strings.TrimSpace(value)
				if value != "" {
					key = strings.TrimPrefix(key, "where_")
					oper, val := tab.GetOper(key, value)
					where += fmt.Sprintf(" AND %s %s ?", key, oper)
					vals = append(vals, val)
				}
			}
		}
	}

	sortKey := param.GetTrimString("sortKey")
	sortValue := param.GetTrimString("sortOrder")

	sql := tab.CountSQL(where)
	err := db.Raw(sql, vals...).Count(&total).Error
	if err != nil {
		m.Error(err.Error() + sql)
		return
	}

	m.Map["code"] = 200
	m.Map["count"] = total
	m.Map["msg"] = ""
	if total == 0 {
		m.Msg("")
		return
	}

	page, limit := m.GetPage()
	first := limit * (page - 1)

	sql = tab.ToSQL(where)

	if tab.DefaultOrder != "" {
		sql += " " + tab.DefaultOrder
	}

	var rows []map[string]interface{}

	if sortValue != "" && sortKey != "" {
		sql += " ORDER BY " + report.GetDialect() + sortKey + report.GetDialect() + " " + sortValue
	}

	//sql += limit
	sql += fmt.Sprintf(" LIMIT %d,%d", first, limit)

	//log.Println("SQL:", sql)
	err = db.Raw(sql, vals...).Find(&rows).Error
	if err != nil {
		m.Error(err.Error() + sql)
		return
	}

	m.Map["data"] = rows
	m.Msg("OK")
}

// Dataset .
func (m *TableController) Dataset(tab *report.Table) {
	m.DatasetDB(defaultDB, tab)
}
